package com.redxiii.spike.pmi.trac;

import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.NumberFormat;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.redxiii.spike.api.pmi.entity.Activity;
import com.redxiii.spike.api.pmi.entity.Issue;
import com.redxiii.spike.api.pmi.entity.IssuePriority;
import com.redxiii.spike.api.pmi.entity.IssueTimeEntry;
import com.redxiii.spike.api.pmi.entity.IssueType;
import com.redxiii.spike.api.pmi.entity.Member;
import com.redxiii.spike.api.pmi.entity.Project;
import com.redxiii.spike.api.pmi.entity.ProjectOutput;
import com.redxiii.spike.pmi.HandlerSession;
import com.redxiii.spike.pmi.commons.PmiDatabase;
import com.redxiii.spike.pmi.commons.PmiSettings;
import com.redxiii.spike.pmi.trac.entity.TracIssue;
import com.redxiii.spike.pmi.trac.entity.TracMember;
import com.redxiii.spike.pmi.trac.entity.TracOutput;
import com.redxiii.spike.pmi.trac.entity.TracPriority;
import com.redxiii.spike.pmi.trac.entity.TracProject;
import com.redxiii.spike.pmi.trac.entity.TracType;
import com.redxiii.util.db.DBAccess;
import com.redxiii.util.db.MySqlDB;
import com.redxiii.util.db.PostgreSQLDB;
import com.redxiii.util.db.SQLUpdateException;


/**
 * @author Daniel Filgueiras
 * 04/05/2011
 */
public class TracDB extends PmiDatabase {

	private static final Logger logger = LoggerFactory.getLogger(TracDB.class);
	private static Lock lock = new ReentrantLock();
	private static TracDB db;
	private static NumberFormat decFormat = new DecimalFormat("0.00", new DecimalFormatSymbols(Locale.US));
	
	private DBAccess dbAccess;
	private String ticketCustomHrsField = "totalhours";
	
	private TracDB(){
		
		PmiSettings settings = HandlerSession.getPmtSettings();
		
		if (settings.getProperties().containsKey("ticketCustomHrsField")){
			ticketCustomHrsField = settings.getProperties().get("ticketCustomHrsField");
		}
	}
	
	public static TracDB getInstance() {
		lock.lock();
		if (db == null) {
			db = new TracDB();
		}
		db.initCache();
		
		lock.unlock();
		
		return db;
	}

	public boolean connect() {
		lock.lock();
		try {
			if (dbAccess == null) {
				logger.info("Creating connection to database");
				PmiSettings settings = HandlerSession.getPmtSettings();
				
				if (settings.getDbHost() == null || settings.getDbLogin() == null || settings.getDbPassword() == null)
					return false;
				
				switch(settings.getDbType()) {
				case MySql:
					dbAccess = new MySqlDB(
							settings.getDbHost(),
							settings.getDbName(),
							settings.getDbLogin(),
							settings.getDbPassword());
					break;
				case PosgreSql:
					dbAccess = new PostgreSQLDB(
							settings.getDbHost(),
							settings.getDbName(),
							settings.getDbLogin(),
							settings.getDbPassword());
					break;
				}
			}
			logger.info("Testing connection");
			return dbAccess.testConnection();
		} catch (Exception e){
			logger.error("Could not connect to database",e);
		} finally {
			lock.unlock();
		}
		return false;
	}
	
	protected Issue loadIssue(Long issueId) {

		logger.trace("Getting issue");
		Map<String, Object> item = dbAccess.executeMapQuery("SELECT * FROM trac.ticket WHERE id = ?", issueId);
		
		if (item == null || item.size() == 0)
			return null;
		
		TracIssue issue = extractTracIssue(item);
		
		return issue;
	}
	
	private TracIssue extractTracIssue(Map<String, Object> item) {
		
		logger.trace("Fetching issue");
		Number issueNum = (Number)item.get("id");
		
		TracIssue issue = new TracIssue(issueNum.longValue(),(String)item.get("summary"));

		issue.setAssignedMember(getMemberFromCache(item.get("owner").toString()));
		issue.setAuthor(getMemberFromCache(item.get("reporter").toString()));
		issue.setProject(getProjectFromCache(item.get("component").toString()));
		issue.setOutput(getOutputFromCache(item.get("milestone").toString()));
		issue.setIssueType(getTypeFromCache(item.get("type").toString()));
		issue.setPriority(getPrioritiesFromCache(item.get("priority").toString()));
		issue.setCreatedDate(extractTracTime(item.get("time")));
		issue.setUpdatedOn(extractTracTime(item.get("changetime")));
		
		issue.setCc(item.get("cc").toString());
		
		String totalhours = getTicketCustom(issue, ticketCustomHrsField);
		if (totalhours == null || totalhours.length() == 0) {
			issue.setSpendTime(0D);
		} else {
			issue.setSpendTime(Double.valueOf(totalhours));
		}
		
		String lastPostNumber = dbAccess.executeScalarQuery("SELECT MAX(oldvalue) FROM trac.ticket_change WHERE ticket = ? and field = 'comment'", issueNum);
		if (lastPostNumber != null && lastPostNumber.matches("^\\d+$") ){
			issue.setCommentNum(Integer.parseInt(lastPostNumber));
			
			
			List<Map<String, Object>> changes = dbAccess.executeListMapQuery("SELECT * FROM trac.ticket_change WHERE ticket = ? and time = ?", issueNum, item.get("changetime"));
			if (changes != null && changes.size() > 0) {
				for (Map<String, Object> tuple : changes) {
					if (tuple.get("field").equals("comment")) {
						issue.setMemberLastUpdate(getMemberFromCache(tuple.get("author").toString()));
						issue.setRawLastUpdate(tuple.get("newvalue").toString());
					}
				}
				
			}
			
			
		} else {
			issue.setCommentNum(0);
		}
		
		return issue;
	}
	
	private Date extractTracTime(Object obj) {
		if (obj != null && obj instanceof Number)
			return new Date(((Number)obj).longValue() * 1000L);
		
		return new Date();
	}
	
	
	public String getTicketCustom(Issue issue, String field) {
		return dbAccess.executeScalarQuery("SELECT value FROM trac.ticket_custom WHERE ticket = ? AND name = ?", issue.getId(), field);
	}

	protected Set<Long> loadIssues(Member member) {
		List<Long> ids = dbAccess.executeListScalarQuery(
				"SELECT id FROM trac.ticket WHERE owner = ? and status <> 'closed' ORDER BY id",
				member.getLogin());
		
		return new HashSet<Long>(ids);
	}
	
	protected Set<Number> loadActiveIssues(Member member) {
		List<Long> ids = dbAccess.executeListScalarQuery(
				"SELECT t.id FROM trac.ticket t WHERE t.status <> 'closed' AND t.id in (" +
						"select distinct(ticket) from ticket_change where author = ? and " +
						"(TIMESTAMP 'epoch' + time * INTERVAL '1 second')::date > CURRENT_DATE - 30" +
					") ORDER BY id",
				member.getLogin());
		
		return new HashSet<Number>(ids);
	}
	
	protected Set<Project> loadProjects() {
		logger.debug("Loading all projects...");
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("SELECT * FROM trac.component");
		if (result == null)
			return null;
		
		HashSet<Project> projects = new HashSet<Project>();
		for (Map<String, Object> item : result) {
			TracProject project = new TracProject(item.get("name").toString());
			
			if (item.get("description") != null && item.get("description").toString().length() > 0)
				project.setName(item.get("description").toString());

			projects.add(project);
		}
		logger.debug("Got {} projects", projects.size());
		
		return projects;
	}
	
	@Override
	protected Set<ProjectOutput> loadOutputs() {
		
		logger.debug("Loading all projects...");
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("SELECT * FROM trac.milestone");
		if (result == null)
			return null;
		
		HashSet<ProjectOutput> projects = new HashSet<ProjectOutput>();
		for (Map<String, Object> item : result) {
			TracOutput outputs = new TracOutput(item.get("name").toString());
			
			if (item.get("description") != null && item.get("description").toString().length() > 0)
				outputs.setName(item.get("description").toString());

			projects.add(outputs);
		}
		logger.debug("Got {} outputs", projects.size());
		
		return projects;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	protected Set<Activity> loadActivities() {
		return Collections.EMPTY_SET;
	}
	
	@Override
	protected Set<IssueType> loadIssueTypes() {
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("select * from trac.enum where type = 'ticket_type'");
		
		if (result == null)
			return null;
		
		Set<IssueType> issueTypes = new HashSet<IssueType>();
		
		for (Map<String, Object> item : result) {
			TracType type = new TracType(item.get("name").toString());
			type.setName(item.get("name").toString());
			
			issueTypes.add(type);
		}
		
		return issueTypes;
	}
	
	@Override
	protected Set<IssuePriority> loadIssuePriorities() {
		List<Map<String, Object>> result = dbAccess.executeListMapQuery("select * from trac.enum where type = 'priority'");
		
		if (result == null)
			return null;
		
		Set<IssuePriority> issuePriorities = new HashSet<IssuePriority>();
		
		for (Map<String, Object> item : result) {
			TracPriority type = new TracPriority(item.get("name").toString());
			type.setName(item.get("name").toString());
			
			issuePriorities.add(type);
		}
		
		return issuePriorities;
	}
	
	@Override
	protected Set<Member> loadMembers() {
		Set<Member> members = new HashSet<Member>();
		
		logger.debug("Loading all members...");
		List<Map<String, Object>> result = dbAccess.executeListMapQuery(
				"select session.sid, value from session inner join session_attribute on (session.sid = session_attribute.sid) where session_attribute.name = 'email'");
			
		if (result == null)
			return null;
		
		for (Map<String, Object> item : result) {
			
			TracMember member = new TracMember(item.get("sid").toString());
			member.setName(member.getId());
			member.setLogin(member.getId());
			member.setMail(item.get("value").toString());
			
			members.add(member);
		}
		logger.debug("Got {} members", members.size());
		
		return members;
	}
	
	
	@Override
	public Issue commitWork(IssueTimeEntry timeEntry) {
		try {
			TracIssue tracissue = (TracIssue)timeEntry.getIssue();
			
			String totalhours = getTicketCustom(tracissue, ticketCustomHrsField);
			if (totalhours == null) {
				logger.warn("Invalid ticket_custom field configuration: {}", ticketCustomHrsField);
				return null;
			} else {
				tracissue.setSpendTime(Double.valueOf(totalhours));	// Update 
			}
			
			long timestamp = updateTicket(tracissue);
			String oldValue = decFormat.format(tracissue.getSpendTime());
			String newValue = decFormat.format(tracissue.getSpendTime() + timeEntry.getWorkedTime());
			
			if (timestamp <= 0L){
				return null;
			}
			
			if (!updateTicketCustom(tracissue, ticketCustomHrsField, newValue)) {
				return null;
			}
						
			if (!addTicketChange(tracissue, timestamp, timeEntry.getMember().getId(), ticketCustomHrsField, oldValue, newValue)){
				return null;
			}
			
			if (timeEntry.getComment() != null && timeEntry.getComment().length() > 0){
				tracissue.setCommentNum(tracissue.getCommentNum()+1);
				String num = Integer.toString(tracissue.getCommentNum());
				if (!addTicketChange(tracissue, timestamp, timeEntry.getMember().getId(), "comment", num, timeEntry.getComment().toString())){
					return null;
				}
			}
			
			tracissue.setSpendTime(tracissue.getSpendTime() + timeEntry.getWorkedTime());
			
		} catch (Exception e) {
			logger.error("Could not update",e);
			return null;
		}
		
		logger.debug("Commit complete");
		return timeEntry.getIssue();
	}
	
	@Override
	public void giveBack(Issue issue, Member member) {
		TracIssue tracissue = (TracIssue)issue;
		
		long timestamp = updateTicket(tracissue, "owner", issue.getAuthor().getId());
		
		tracissue.setCommentNum(tracissue.getCommentNum()+1);
		String num = Integer.toString(tracissue.getCommentNum());
		
		if (timestamp > 0L) {
			addTicketChange(tracissue, timestamp, member.getId(), "owner", issue.getAssignedMember().getId(), issue.getAuthor().getId());
			addTicketChange(tracissue, timestamp, member.getId(), "comment", num, "");
		}
	}
	
	@Override
	public void closeIssue(Issue issue, Member member) {
		// TODO Auto-generated method stub
		
	}
	
	private long updateTicket(Issue issue) {
		return updateTicket(issue, null, null);
	}
	
	private long updateTicket(Issue issue, String field, Object value) {
		
		long timestamp = (long)(System.currentTimeMillis() / 1000);
		
		StringBuilder query = new StringBuilder("UPDATE trac.ticket SET changetime = ?");
		if (field != null) {
			query.append(", ").append(field).append(" = ?");
		}
		query.append(" WHERE id = ?");
		
		logger.debug("Updating ticket / field {}", field);
		try {
			Object[] params;
			if (field != null) {
				params = new Object[]{timestamp, value, issue.getId()};
			} else {
				params = new Object[]{timestamp, issue.getId()};
			}
			if (dbAccess.executeUpdate(query.toString(), params)){
				return timestamp;
			}
		} catch (SQLUpdateException e) {
			logger.error("Unable to update {} from issue {}", new Object[]{field, issue.getId()}, e);
		}
		return 0;
	}
		
	private boolean updateTicketCustom(Issue issue, String name, String newValue) {
		
		logger.debug("Updating ticket_custom / field {}", name);
		try {
			if (dbAccess.executeUpdate("UPDATE trac.ticket_custom SET value = ? WHERE ticket = ? AND name = ?",
					newValue,
					issue.getId(),
					name)){
				return true;
			}
		} catch (SQLUpdateException e) {
			logger.error("Unable to update ticket_custom field {} from issue {}", new Object[]{name, issue.getId()}, e);
		}
		return false;
	}
	
	public boolean addTicketChange(Issue issue, long timestamp, String memberId, String field, String oldValue, String newValue) {
		
		logger.debug("Insert ticket_change / field: {} / oldValue: {} / newValue: {}", new Object[]{field, oldValue, newValue});
		try {
			if (dbAccess.executeUpdate("INSERT INTO trac.ticket_change (ticket, time, author, field, oldvalue, newvalue) VALUES (?, ?, ?, ?, ?, ?)", 
					issue.getId(),
					timestamp,
					memberId,
					field,
					oldValue,
					newValue)){
				return true;
			}
		} catch (SQLUpdateException e) {
			logger.error("Unable to insert ticket_change field {} from issue {}", new Object[]{field, issue.getId()}, e);
		}
		
		return false;
	}
	
	public void setTicketCustomHrsField(String ticketCustomHrsField) {
		this.ticketCustomHrsField = ticketCustomHrsField;
	}
}
